package com.AEWMS.Service;

import com.AEWMS.Pojo.StudentAttendanceInfoView;
import com.AEWMS.Utlis.DbCon;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class AttendDetailService {
    private Connection conn = null ;
    private PreparedStatement pstmt = null ;

    public AttendDetailService() {//改加上姓名之后的输出数据
        DbCon dbcon;
        try {
            dbcon = new DbCon();
            this.conn = dbcon.getConnection();
        } catch (Exception e) {
            System.out.println("AttendanceWarningService 连接数据库失败");
            e.printStackTrace();
        }
    }

    public void closeCon(){
        try {
            this.conn.close();
        } catch (Exception e) {
            System.out.println("数据库连接关闭");
            e.printStackTrace();
        }
    }

    /**
     * 学生查找考勤详情
     * @param id 通过id
     * @return 返回一个考勤数组
     */
    public ArrayList<StudentAttendanceInfoView> findAttendDetailById(String id){
        ArrayList<StudentAttendanceInfoView> StuAttendDetList = new ArrayList<>();
        String sql = "SELECT * FROM student_attendance_info_view WHERE student_number = ? ";
        try {
            this.pstmt = conn.prepareStatement(sql);//将sql语句放到数据库的准备对象
            this.pstmt.setString(1,id);//将传入id值替换问号放在SQL
            ResultSet rs = this.pstmt.executeQuery();//执行查询返回结果集
            while (rs.next()){
                StudentAttendanceInfoView s = new StudentAttendanceInfoView();//按行查找，为false时查询结束
                s.setStudentNumber(rs.getString(1));
                s.setStudentRealName(rs.getString(2));
                s.setStudentSex(rs.getString(3));
                s.setCollegeName(rs.getString(4));
                s.setGradeId(rs.getString(5));
                s.setMajorName(rs.getString(6));
                s.setClassName(rs.getString(7));
                s.setSemesterYear(rs.getString(8));
                s.setSemesterType(rs.getString(9));
                s.setWeek(rs.getString(10));
                s.setAttendanceTypeName(rs.getString(11));
                s.setAttendanceStandard(rs.getFloat(12));
                s.setAttendanceTimes(rs.getInt(13));
                s.setAbsenteeismSection(rs.getString(14));
                StuAttendDetList.add(s);
            }
        }
        catch (Exception e) {
            System.out.println("findAttendDetailById 失败");
            System.out.println(e);
        }
        return StuAttendDetList;
    }
    /**
     * 学生查找考勤详情
     * @param id 通过id
     * @return 返回一个考勤数组
     */
    public ArrayList<StudentAttendanceInfoView> findAttendDetailByIdYearType(String id,String year,String type){
        ArrayList<StudentAttendanceInfoView> StuAttendDetList = new ArrayList<>();
        String sql = "SELECT * FROM student_attendance_info_view WHERE student_number = ? AND semester_year = ? AND semester_type = ?";
        try {
            this.pstmt = conn.prepareStatement(sql);//将sql语句放到数据库的准备对象
            this.pstmt.setString(1,id);
            this.pstmt.setString(2,year);
            this.pstmt.setString(3,type);
            ResultSet rs = this.pstmt.executeQuery();//执行查询返回结果集
            while (rs.next()){
                StudentAttendanceInfoView s = new StudentAttendanceInfoView();//按行查找，为false时查询结束
                s.setStudentNumber(rs.getString(1));
                s.setStudentRealName(rs.getString(2));
                s.setStudentSex(rs.getString(3));
                s.setCollegeName(rs.getString(4));
                s.setGradeId(rs.getString(5));
                s.setMajorName(rs.getString(6));
                s.setClassName(rs.getString(7));
                s.setSemesterYear(rs.getString(8));
                s.setSemesterType(rs.getString(9));
                s.setWeek(rs.getString(10));
                s.setAttendanceTypeName(rs.getString(11));
                s.setAttendanceStandard(rs.getFloat(12));
                s.setAttendanceTimes(rs.getInt(13));
                s.setAbsenteeismSection(rs.getString(14));
                StuAttendDetList.add(s);
            }
        }
        catch (Exception e) {
            System.out.println("findAttendDetailByIdYearType 失败");
            System.out.println(e);
        }
        return StuAttendDetList;
    }
}
